if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[p_chkbrelationtb]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[p_chkbrelationtb]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE   procedure dbo.p_chkbrelationtb
@tablename sysname,
@keyfield sysname,
@keyvalue integer,
@found integer output,
@reltable sysname output
as
declare @fktable sysname, @fkfield sysname 
declare @tempint integer
declare @strsql nvarchar(255)
set @found=0
set nocount on
set @reltable=''


declare findcur cursor forward_only read_only for 
  select object_name(ref.fkeyid), col_name(ref.fkeyid, fk.fkey) 
  from sysreferences ref 
   join sysobjects obj on ref.constid=obj.id 
   join sysforeignkeys fk on obj.id=fk.constid
  where object_name(ref.rkeyid)=@tablename and  col_name(ref.rkeyid, fk.rkey)=@keyfield
    and object_name(ref.fkeyid)<>@tablename+'dtl'
open findcur
fetch next from findcur into @fktable, @fkfield 
while @@fetch_status=0
begin
  set @tempint=0
  set @strsql='select top 1 @tempint=1 from '+@fktable+' where '+ @fkfield+'='+cast(@keyvalue as varchar(10))
  exec sp_executesql @strsql, N'@tempint int output', @tempint output
  if @tempint > 0
  begin
    set @found = 1
    set @reltable=@fktable 
    break
  end
  fetch next from findcur into @fktable, @fkfield 
end
close findcur
deallocate findcur




GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

